import akshare as ak
import pandas as pd
from datetime import datetime, timedelta

# 1. 获取过去一年日期范围
# 数据从13年10月18日开始有铁矿石
# end_date = datetime.now().strftime("%Y%m%d")
# start_date = (datetime.now() - timedelta(days=365)).strftime("%Y%m%d")


# 2. 获取焦煤主连数据
def get_main_contract_data(symbol):
    # 默认从开头取到现在
    df = ak.futures_main_sina(symbol=symbol)
    df['date'] = pd.to_datetime(df['日期']).dt.strftime('%Y-%m-%d')
    # df = df[(df['date'] >= start) & (df['date'] <= end)]
    return df[['date', '开盘价', '收盘价']].rename(columns={'开盘价': 'open', '收盘价': 'close'})


def ratio_a_b(goods_left, goods_right, goods_left_cn, goods_right_cn):
    GOODS_LEFT_OPEN = 'open_' + goods_left
    GOODS_RIGHT_OPEN = 'open_' + goods_right
    GOODS_LEFT_CLOSE = 'close_' + goods_left
    GOODS_RIGHT_CLOSE = 'close_' + goods_right
    GOODS_LEFT_RIGHT_OPEN_RATIO = goods_left + '/' + goods_right + '_Open_Ratio'
    GOODS_LEFT_RIGHT_CLOSE_RATIO = goods_left + '/' + goods_right + '_Close_Ratio'
    GOODS_LEFT_RIGHT_RATIO_DIFF = goods_left + '/' + goods_right + '_Ratio_Diff'

    # 获取黄金(AU0)和白银(AG0)数据
    au_data = get_main_contract_data(goods_left + "0")
    ag_data = get_main_contract_data(goods_right + "0")

    # 3. 合并数据并计算比值
    # 合并黄金白银
    merged_data = pd.merge(au_data, ag_data, on='date', suffixes=('_' + goods_left, '_' + goods_right))

    # 计算黄精、白银套利
    merged_data[GOODS_LEFT_RIGHT_OPEN_RATIO] = merged_data[GOODS_LEFT_OPEN] / merged_data[GOODS_RIGHT_OPEN]
    merged_data[GOODS_LEFT_RIGHT_CLOSE_RATIO] = merged_data[GOODS_LEFT_CLOSE] / merged_data[GOODS_RIGHT_CLOSE]
    merged_data[GOODS_LEFT_RIGHT_RATIO_DIFF] = merged_data[GOODS_LEFT_RIGHT_OPEN_RATIO] - merged_data[
        GOODS_LEFT_RIGHT_CLOSE_RATIO]

    # 4. 导出Excel（优化列名与格式）
    output_columns = {
        'date': '日期',
        GOODS_LEFT_OPEN: goods_left_cn + '开盘',
        GOODS_LEFT_CLOSE: goods_left_cn + '收盘',
        GOODS_RIGHT_OPEN: goods_right_cn + '开盘',
        GOODS_RIGHT_CLOSE: goods_right_cn + '收盘',

        GOODS_LEFT_RIGHT_OPEN_RATIO: goods_left + '/' + goods_right + '开盘比值',
        GOODS_LEFT_RIGHT_CLOSE_RATIO: goods_left + '/' + goods_right + '收盘比值',
        GOODS_LEFT_RIGHT_RATIO_DIFF: goods_left + '/' + goods_right + '收开差',
    }
    merged_data.rename(columns=output_columns, inplace=True)
    merged_data.to_excel(goods_left_cn + goods_right_cn + "比值.xlsx", index=False, engine='openpyxl')